import webview
from webview.dom import DOMEventHandler
import xlrd
import pandas

def read_xls_to_dict(file_path):
    # 打开工作簿
    workbook = xlrd.open_workbook(file_path)

    # 获取第一个工作表
    sheet = workbook.sheet_by_index(0)

    # 获取列标题
    headers = [sheet.cell_value(0, index) for index in range(sheet.ncols)]

    # 初始化字典列表
    data_dict_list = []

    # 遍历工作表中的行
    for row_idx in range(1, sheet.nrows):  # 从第二行开始，因为第一行是标题
        row_data = {}
        for col_idx, header in enumerate(headers):
            row_data[str(header)] = sheet.cell_value(row_idx, col_idx)
        data_dict_list.append(row_data)

    return data_dict_list


def click_handler(e):
    open_file_dialog(window)


def scroll_handler(e):
    scroll_top = window.dom.window.node['scrollY']
    print(f'Scroll position {scroll_top}')


def open_file_dialog(window):
    file_types = ('Excel Files (*.xlsx;*.xls)', 'All files (*.*)')

    result = window.create_file_dialog(
        webview.OPEN_DIALOG, allow_multiple=True, file_types=file_types
    )
    print(result)
    if result:
        # 打开工作簿
        res = read_xls_to_dict(result[0])
        print(res)
        df = pandas.DataFrame(res)
        print(df)

        new_df = pandas.DataFrame(columns=['A','B','C'])
        new_df['A'] = df['1.0']
        print(new_df)
        new_df.to_excel('out.xlsx', index=False)


def bind(window):
    window.dom.document.events.scroll += scroll_handler

    button = window.dom.get_element('#button')
    button.events.click += click_handler


if __name__ == '__main__':
    window = webview.create_window(
        'DOM Event Example', html='''
            <html>
                <head>
                <style>
                    button {
                        font-size: 100%;
                        padding: 0.5rem;
                        margin: 0.3rem;
                        text-transform: uppercase;
                    }
                </style>
                </head>
                <body style="height: 200vh;">
                    <div>
                        <button id="button">Click me</button>
                    </div>
                </body>
            </html>
        '''
    )
    webview.start(bind, window)
